package use_prepareStatement;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Scanner;

public class use_prepareStatement {
    public static void main(String[] args) throws SQLException {
        initDataSource();//初始化DataSource，一次初始化、多次使用
        Scanner scanner = new Scanner(System.in);

        //使用Statement的写法
        try (Connection c = getConnection()) {
            //查询以下sql：select * from exam_result where english> 某个值
            //这个值需要用户输入
            System.out.print("请输入英语分数获奖分数>");
            int ei = scanner.nextInt();
            System.out.print("请输入语文分数获奖分数>");
            int ci = scanner.nextInt();
            try (Statement s = c.createStatement()) {
                //自己手动拼接sql，很容易写错
                String sql = "select * from exam_result where english > " + ei + " and chinese > " + ci;
                System.out.println(sql);
                try (ResultSet resultSet = s.executeQuery(sql)) {
                    while (resultSet.next()) {
                        String id = resultSet.getString("id");
                        String name = resultSet.getString("name");
                        String english = resultSet.getString("english");
                        String chinese = resultSet.getString("chinese");
                        String math = resultSet.getString("math");
                        System.out.println(id + "," + name + "," + english + "," + chinese + "," + math + ",");
                    }
                }
            }
        }

        //使用prepareStatement的方式
        try (Connection c = getConnection()) {

            //提前把sql写好，使用？作为占位符
            String sql = "select * from exam_result where english > ? and chinese > ? ";
            try (PreparedStatement p = c.prepareStatement(sql)) {
                //查询以下sql：select * from exam_result where english> 某个值
                //这个值需要用户输入
                System.out.print("请输入英语分数获奖分数>");
                int ei = scanner.nextInt();
                System.out.print("请输入语文分数获奖分数>");
                int ci = scanner.nextInt();
                p.setInt(1, ei);
                p.setInt(2, ci);
                try (ResultSet r = p.executeQuery()) {
                    while (r.next()) {
                        String id = r.getString("id");
                        String name = r.getString("name");
                        String english = r.getString("english");
                        String chinese = r.getString("chinese");
                        String math = r.getString("math");
                        System.out.println(" "+id + "," + name + "," + english + "," + chinese + "," + math + ",");
                    }
                }
            }
        }
    }

    private static DataSource dataSource = null;

    private static void initDataSource() {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setServerName("127.0.0.1");
        mysqlDataSource.setPort(3306);
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("missh111");
        mysqlDataSource.setDatabaseName("testbase");
        mysqlDataSource.setUseSSL(false);
        mysqlDataSource.setCharacterEncoding("utf8");
        dataSource = mysqlDataSource;
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}
